let server = require("express")();
let expressStatic = require("express-static")
let mysql = require("mysql")
const port = 8082;
// let address = "http://10.35.170.201:8082";

let sql = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'sms',
    timezone: "08:00"
});

sql.connect();
// 读取数据库
server.get("/getstudentList", (request, response) => {
    sql.query(`SELECT * FROM student`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send(JSON.stringify(data));
        }
    })
})

// 删除
server.get("/deleteStudent", (request, response) => {
    sql.query(`DELETE FROM student WHERE id=${request.query.id}`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success");
        }
    })
})

// 添加
server.get("/addStudent", (request, response) => {
    let q = request.query;
    sql.query(`INSERT INTO student (name,age,sex,city,joinDate) VALUES ("${q.name}","${q.age}","${q.sex}","${q.city}","${q.joinDate}")`, (error, data) => {
        if (error) {
            console.log(error);
            response.send("error");
        } else {
            response.send("success");
            console.log(`新增学生${q.name}操作成功`);
        }
    })
})

//注册界面 
server.get("/register", (request, response) => {
    // request.query
    let queq = request.query;
    sql.query(`INSERT INTO user (realname,username,password,usertype) VALUES ("${queq.realname}","${queq.username}","${queq.password}","${queq.usertype}")`, (error, data) => {
        if (error) {
            console.log("error");
            response.send("error");
        } else {
            response.send("success");
            console.log(`${queq.username}用户注册成功`)
        }
    })
})

// 登录
server.get("/login", (request, response) => {
    // request.query
    let que = request.query;
    sql.query(`SELECT * FROM user WHERE username="${que.username}" AND password="${que.password}"`, (error, data) => {
        if (error) {
            console.log("error");
            response.send("3"); //数据库出错，返回3
        } else {
            if (data.length === 0) {
                response.send("2"); //查不到数据返回2
            } else {
                // response.send("1") //都查收到返回1
                response.send(JSON.stringify(data));
            }
            console.log(data);
        }
    })
})



// 编辑功能
server.get("/changeStudent", (request, response) => {
    console.log(request.query.id)
    console.log(request.query.name)
    let p = request.query;
    sql.query(`UPDATE student SET name="${p.name}",sex="${p.sex}",age="${p.age}",city="${p.city}",joinDate="${p.joinDate}" WHERE id="${p.id}"`, (error, data) => {
        if (error) {
            console.log(error)
            response.send("error")
        } else {
            response.send("success")
        }
    })
})

server.use(expressStatic(__dirname + '/static'));

server.listen(port);
console.log(`server is running at ${port}`);